<!doctype html><html lang=zh-cn data-theme=dark>
<head>
<meta charset=utf-8>
<meta name=viewport content="width=device-width">
<meta name=theme-color content="#222" media="(prefers-color-scheme: dark)">
<meta name=generator content="Hugo 0.92.2">
<link rel="shortcut icon" type=image/x-icon href=/imgs/icons/favicon.ico>
<link rel=icon type=image/x-icon href=/imgs/icons/favicon.ico>
<link rel=icon type=image/png sizes=16x16 href=/imgs/icons/favicon_16x16_next.png>
<link rel=icon type=image/png sizes=32x32 href=/imgs/icons/favicon_32_32_next.png>
<link rel=apple-touch-icon sizes=180x180 href=/imgs/icons/apple_touch_icon_next.png>
<meta itemprop=name content="mySQl查询语句大全">
<meta itemprop=description content="mySQl查询语句大全">
<meta itemprop=datePublished zgotmplz>
<meta itemprop=dateModified zgotmplz>
<meta itemprop=image content="https://dzmm.gitee.io/imgs/hugo_next_avatar.png">
<meta itemprop=keywords content="mysql,php">
<link type=text/css rel=stylesheet href=https://cdn.staticfile.org/font-awesome/6.1.2/css/all.min.css>
<link type=text/css rel=stylesheet href=https://cdn.staticfile.org/animate.css/3.1.1/animate.min.css>
<link type=text/css rel=stylesheet href=https://cdn.staticfile.org/viewerjs/1.11.0/viewer.min.css>
<link rel=stylesheet href=/css/main.min.659f2a18a0de6aa5054906011a584c8ff887591456aea225eabf98f2cfae91b7.css>
<style type=text/css>.post-footer,.flinks-list-footer hr:after{content:"~ 我可是有底线的哟 ~"}</style>
<script type=text/javascript>(function(){localDB={set:function(b,c,a){if(a===0)return;const d=new Date,e=a*864e5,f={value:c,expiry:d.getTime()+e};localStorage.setItem(b,JSON.stringify(f))},get:function(a){const b=localStorage.getItem(a);if(!b)return void 0;const c=JSON.parse(b),d=new Date;return d.getTime()>c.expiry?(localStorage.removeItem(a),void 0):c.value}},theme={active:function(){const a=localDB.get('theme');if(a==void 0)return;theme.toggle(a),window.matchMedia("(prefers-color-scheme: dark)").addListener(function(a){theme.toggle(a.matches?'dark':'light')})},toggle:function(a){document.documentElement.setAttribute('data-theme',a),localDB.set('theme',a,2);const b=document.querySelector('iframe.giscus-frame');if(b){const c={setConfig:{theme:a}};b.contentWindow.postMessage({giscus:c},'https://giscus.app')}}},theme.active()})(window)</script>
<script class=next-config data-name=page type=application/json>{"comments":false,"isHome":false,"isPage":true,"path":"2022-12-21-mysqlyuju.html","permalink":"https://dzmm.gitee.io/post/2022-12-21-mysqlyuju.html","title":"mySQl查询语句大全","waline":{"js":[{"alias":"waline","alias_name":"@waline/client","file":"dist/pageview.js","name":"pageview","version":"2.13.0"},{"alias":"waline","alias_name":"@waline/client","file":"dist/comment.js","name":"comment","version":"2.13.0"}]}}</script>
<script type=text/javascript>document.addEventListener('DOMContentLoaded',()=>{var a=document.createElement('script');a.charset="UTF-8",a.src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js",a.async=!1,a.defer=!0,document.head.appendChild(a),a.onload=function(){NexT.utils.fmtBusuanzi()}})</script>
<title>mySQl查询语句大全 - 烂笔头</title>
<noscript>
<link rel=stylesheet href=/css/noscript.css>
</noscript>
</head>
<body itemscope itemtype=http://schema.org/WebPage class=use-motion>
<div class=headband></div>
<main class=main>
<header class=header itemscope itemtype=http://schema.org/WPHeader>
<div class=header-inner>
<div class=site-brand-container>
<div class=site-nav-toggle>
<div class=toggle aria-label role=button>
<span class=toggle-line></span>
<span class=toggle-line></span>
<span class=toggle-line></span>
</div>
</div>
<div class=site-meta>
<a href=/ class=brand rel=start>
<i class=logo-line></i>
<h1 class=site-title>烂笔头</h1>
<i class=logo-line></i>
</a>
<p class=site-subtitle itemprop=description>为 Hugo 打造的主题</p>
</div>
<div class=site-nav-right>
<div class="toggle popup-trigger">
<i class="fa fa-search fa-fw fa-lg"></i>
</div>
</div>
</div>
<nav class=site-nav>
<ul class="main-menu menu">
<li class="menu-item menu-item-home">
<a href=/ class=hvr-icon-pulse rel=section>
<i class="fa fa-home hvr-icon"></i>首页
</a>
</li>
<li class="menu-item menu-item-about">
<a href=/ class=hvr-icon-pulse rel=section>
<i class="fa fa-user hvr-icon"></i>关于
</a>
</li>
<li class="menu-item menu-item-flinks">
<a href=/ class=hvr-icon-pulse rel=section>
<i class="fa fa-thumbs-up hvr-icon"></i>站点示例
</a>
</li>
<li class="menu-item menu-item-archives">
<a href=/ class=hvr-icon-pulse rel=section>
<i class="fa fa-archive hvr-icon"></i>归档
<span class=badge>12</span>
</a>
</li>
<li class="menu-item menu-item-commonweal">
<a href=/404.html class=hvr-icon-pulse rel=section>
<i class="fa fa-heartbeat hvr-icon"></i>公益 404
</a>
</li>
<li class="menu-item menu-item-search">
<a role=button class="popup-trigger hvr-icon-pulse">
<i class="fa fa-search fa-fw hvr-icon"></i>搜索
</a>
</li>
</ul>
</nav>
<div class=search-pop-overlay>
<div class="popup search-popup">
<div class=search-header>
<span class=search-icon>
<i class="fa fa-search"></i>
</span>
<div class=search-input-container>
<input autocomplete=off autocapitalize=off maxlength=80 placeholder=搜索... spellcheck=false type=search class=search-input>
</div>
<span class=popup-btn-close role=button>
<i class="fa fa-times-circle"></i>
</span>
</div>
<div class="search-result-container no-result">
<div class=search-result-icon>
<i class="fa fa-spinner fa-pulse fa-5x"></i>
</div>
</div>
</div>
</div>
</div>
<div class="toggle sidebar-toggle" role=button>
<span class=toggle-line></span>
<span class=toggle-line></span>
<span class=toggle-line></span>
</div>
<aside class=sidebar>
<div class="sidebar-inner sidebar-nav-active sidebar-toc-active">
<ul class=sidebar-nav>
<li class=sidebar-nav-toc>
文章目录
</li>
<li class=sidebar-nav-overview>
站点概览
</li>
</ul>
<div class=sidebar-panel-container>
<div class="post-toc-wrap sidebar-panel">
<div class="post-toc animated"><nav id=TableOfContents>
<ul>
<li><a href=#综合使用-查询-目录>综合使用 查询 目录：</a></li>
<li><a href=#常规设置操作>常规设置操作</a>
<ul>
<li><a href=#1服务器设置远程访问>1.服务器设置远程访问</a></li>
<li><a href=#2常用数据类型>2.常用数据类型</a></li>
<li><a href=#3查看数据列表>3.查看数据列表</a></li>
<li><a href=#4进入数据库>4.进入数据库</a></li>
<li><a href=#5创建数据库>5.创建数据库</a></li>
<li><a href=#6删除数据库>6.删除数据库</a></li>
<li><a href=#7查看表>7.查看表</a></li>
<li><a href=#8创建表>8.创建表</a></li>
<li><a href=#9删除表>9.删除表</a></li>
<li><a href=#10修改表>10.修改表</a></li>
<li><a href=#11增加数据>11.增加数据</a></li>
<li><a href=#12修改数据>12.修改数据</a></li>
<li><a href=#13删除数据>13.删除数据</a></li>
<li><a href=#14数据的简单查询>14.数据的简单查询</a></li>
<li><a href=#15几个简单的基本的sql语句>15.几个简单的基本的sql语句</a></li>
<li><a href=#16like-的用法>16.like 的用法</a></li>
</ul>
</li>
<li><a href=#单表查询查询>单表查询查询</a>
<ul>
<li><a href=#1综合使用>1.综合使用</a></li>
<li><a href=#2where-查询>2.where 查询</a></li>
<li><a href=#2聚合查询-以组为单位统计组内数据聚合查询聚集到一起合成为一个结果>2.聚合查询 ：以组为单位统计组内数据&#187;>聚合查询(聚集到一起合成为一个结果)</a></li>
<li><a href=#3having-过滤查询>3.having 过滤查询</a></li>
<li><a href=#4distinct-去重>4.distinct 去重</a></li>
<li><a href=#5order-by-排序>5.order by 排序</a></li>
<li><a href=#6limit-限制展示条数>6.limit 限制展示条数</a></li>
<li><a href=#8replace-替换>8.replace 替换</a></li>
</ul>
</li>
<li><a href=#多表查询>多表查询</a>
<ul>
<li><a href=#1内连接左连接右连接全连接>1.内连接、左连接、右连接、全连接</a></li>
<li><a href=#2子查询>2.子查询</a></li>
</ul>
</li>
</ul>
</nav></div>
</div>
<div class="site-overview-wrap sidebar-panel">
<div class="site-author site-overview-item animated" itemprop=author itemscope itemtype=http://schema.org/Person>
<img class=site-author-image itemprop=image alt="NexT 主题" src=/imgs/img-lazy-loading.gif data-src=/imgs/hugo_next_avatar.png>
<p class=site-author-name itemprop=name>NexT 主题</p>
<div class=site-description itemprop=description>保持简单的易用性和强大的功能。</div>
</div>
<div class="site-state-wrap site-overview-item animated">
<nav class=site-state>
<div class="site-state-item site-state-posts">
<a href=/archives/>
<span class=site-state-item-count>12</span>
<span class=site-state-item-name>日志</span>
</a>
</div>
<div class="site-state-item site-state-categories">
<a href=/categories/>
<span class=site-state-item-count>8</span>
<span class=site-state-item-name>分类</span>
</a>
</div>
<div class="site-state-item site-state-tags">
<a href=/tags/>
<span class=site-state-item-count>17</span>
<span class=site-state-item-name>标签</span>
</a>
</div>
</nav>
</div>
<div class="links-of-social site-overview-item animated">
<span class=links-of-social-item>
<a href=https://github.com/caoqing title="Github → https://github.com/caoqing" rel=noopener target=_blank>
Github
</a>
</span>
</div>
<div class="cc-license animated" itemprop=license>
<a href=https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh class=cc-opacity rel=noopener target=_blank title=共享知识>
<img src=/imgs/img-lazy-loading.gif data-src=/imgs/cc/big/by_nc_sa.svg alt=共享知识>
</a>
</div>
<div class="links-of-blogroll site-overview-item animated">
<div class=links-of-blogroll-title>
<i class="fa fa-globe fa-fw"></i>
友情链接
</div>
<ul class=links-of-blogroll-list>
<li class=links-of-blogroll-item>
<a href=https://gitee.com/hugo-next/hugo-theme-next title=https://gitee.com/hugo-next/hugo-theme-next target=_blank>Hugo-NexT</a>
</li>
<li class=links-of-blogroll-item>
<a href=https://lisenhui.cn title=https://lisenhui.cn target=_blank>凡梦星尘空间站</a>
</li>
</ul>
</div>
</div>
</div>
</div>
<div id=siteinfo-card-widget class=sidebar-card-widget>
<div class=item-headline>
<i class="fas fa-chart-line"></i>
<span>网站资讯</span>
</div>
<div class=siteinfo>
<div class=siteinfo-item>
<div class=item-name><i class="fa-solid fa-calendar-check"></i>已运行：</div>
<div class=item-count id=runTimes data-publishdate=2022-06-02T11:52:03+08:00></div>
</div>
<div class=siteinfo-item>
<div class=item-name>
<i class="fas fa fa-user"></i>总访客数：
</div>
<div class=item-count id=busuanzi_value_site_uv><i class="fa fa-sync fa-spin"></i></div>
</div>
<div class=siteinfo-item>
<div class=item-name>
<i class="fas fa fa-eye"></i>页面浏览：
</div>
<div class=item-count id=busuanzi_value_site_pv><i class="fa fa-sync fa-spin"></i></div>
</div>
<div class=siteinfo-item>
<div class=item-name><i class="fa fa-font"></i>总字数：</div>
<div class=item-count id=wordsCount data-count=12210></div>
</div>
<div class=siteinfo-item>
<div class=item-name><i class="fa fa-mug-hot"></i>阅读约：</div>
<div class=item-count id=readTimes data-times=31></div>
</div>
<div class=siteinfo-item>
<div class=item-name><i class="fa fa-clock-rotate-left"></i>最后更新于：</div>
<div class=item-count id=last-push-date data-lastpushdate=2022-12-21T00:00:00+00:00></div>
</div>
</div>
</div>
</aside>
<div class=sidebar-dimmer></div>
</header>
<div class=tool-buttons>
<div id=toggle-theme class=button title=深浅模式切换>
<i class="fas fa-adjust"></i>
</div>
<div class=back-to-top role=button title=返回顶部>
<i class="fa fa-arrow-up"></i>
<span>0%</span>
</div>
</div>
<div class=reading-progress-bar></div>
<a role=button class="book-mark-link book-mark-link-fixed"></a>
<a href=https://github.com/hugo-next rel="noopener external nofollow noreferrer" target=_blank title="Follow me on GitHub" class="exturl github-corner"><svg width="80" height="80" viewBox="0 0 250 250" aria-hidden="true"><path d="M0 0 115 115h15l12 27L250 250V0z"/><path d="M128.3 109C113.8 99.7 119 89.6 119 89.6 122 82.7 120.5 78.6 120.5 78.6 119.2 72 123.4 76.3 123.4 76.3 127.3 80.9 125.5 87.3 125.5 87.3 122.9 97.6 130.6 101.9 134.4 103.2" fill="currentcolor" style="transform-origin:130px 106px" class="octo-arm"/><path d="M115 115C114.9 115.1 118.7 116.5 119.8 115.4l13.9-13.8C136.9 99.2 139.9 98.4 142.2 98.6 133.8 88 127.5 74.4 143.8 58 148.5 53.4 154 51.2 159.7 51 160.3 49.4 163.2 43.6 171.4 40.1 171.4 40.1 176.1 42.5 178.8 56.2 183.1 58.6 187.2 61.8 190.9 65.4 194.5 69 197.7 73.2 200.1 77.6 213.8 80.2 216.3 84.9 216.3 84.9 212.7 93.1 206.9 96 205.4 96.6 205.1 102.4 203 107.8 198.3 112.5 181.9 128.9 168.3 122.5 157.7 114.1 157.9 116.9 156.7 120.9 152.7 124.9L141 136.5C139.8 137.7 141.6 141.9 141.8 141.8z" fill="currentcolor" class="octo-body"/></svg>
</a>
<noscript>
<div class=noscript-warning>Theme NexT works best with JavaScript enabled</div>
</noscript>
<div class="main-inner post posts-expand">
<div class=post-block>
<article itemscope itemtype=http://schema.org/Article class=post-content lang>
<link itemprop=mainEntityOfPage href=https://dzmm.gitee.io/post/2022-12-21-mysqlyuju.html>
<span hidden itemprop=author itemscope itemtype=http://schema.org/Person>
<meta itemprop=image content="/imgs/hugo_next_avatar.png">
<meta itemprop=name content="NexT 主题">
</span>
<span hidden itemprop=publisher itemscope itemtype=http://schema.org/Organization>
<meta itemprop=name content="NexT 主题">
<meta itemprop=description content="保持简单的易用性和强大的功能。">
</span>
<span hidden itemprop=post itemscope itemtype=http://schema.org/CreativeWork>
<meta itemprop=name content="mySQl查询语句大全">
<meta itemprop=description content="mySQl查询语句大全">
</span>
<header class=post-header>
<h1 class=post-title itemprop="name headline">
mySQl查询语句大全
</h1>
<div class=post-meta-container>
<div class=post-meta-items>
<span class=post-meta-item>
<span class=post-meta-item-icon>
<i class="far fa-calendar"></i>
</span>
<span class=post-meta-item-text title=发表于>
发表于：
</span>
<time title="创建时间：2022-12-21 00:00:00 +0000 UTC" itemprop="dateCreated datePublished" datetime="2022-12-21 00:00:00 +0000 UTC">
2022-12-21
</time>
</span>
<span class=post-meta-item>
<span class=post-meta-item-icon>
<i class="far fa-calendar-check"></i>
</span>
<span class=post-meta-item-text title=更新于>
更新于：
</span>
<time title=修改时间：2022-12-22T19:46:45+08:00 itemprop=dateModified datetime=2022-12-22T19:46:45+08:00>2022-12-22</time>
</span>
<span class=post-meta-item>
<span class=post-meta-item-icon>
<i class="far fa-folder-open"></i>
</span>
<span class=post-meta-item-text title=分类于>
分类于：
</span>
<span itemprop=about itemscope itemtype=http://schema.org/Thing>
<a href=/categories/mysql itemprop=url rel=index>
<span itemprop=name>mysql</span>
</a>
</span>
</span>
</div>
<div class=post-meta-items>
<span class=post-meta-item title=字数>
<span class=post-meta-item-icon>
<i class="far fa-file-word"></i>
</span>
<span class=post-meta-item-text>字数：</span>
<span>4385</span>
</span>
<span class=post-meta-item title=阅读>
<span class=post-meta-item-icon>
<i class="far fa-clock"></i>
</span>
<span class=post-meta-item-text>阅读：&ap;</span>
<span>9分钟</span>
</span>
<span class=post-meta-item title=浏览>
<span class=post-meta-item-icon>
<i class="far fa-eye"></i>
</span>
<span class=post-meta-item-text>
浏览：
</span>
<span id=busuanzi_value_page_pv data-path=/post/2022-12-21-mysqlyuju.html>
<i class="fa fa-sync fa-spin"></i>
</span>
</span>
</div>
</div>
</header>
<div class="post-body autonumber" itemprop=articleBody>
<h2 id=综合使用-查询-目录>综合使用 查询 目录：
<a class=header-anchor href=#%e7%bb%bc%e5%90%88%e4%bd%bf%e7%94%a8-%e6%9f%a5%e8%af%a2-%e7%9b%ae%e5%bd%95></a>
</h2><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span><span style=color:#75715e>----综合使用
</span><span style=color:#75715e></span><span style=color:#960050;background-color:#1e0010>书写顺序</span>
<span style=color:#66d9ef>select</span> <span style=color:#66d9ef>distinct</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> <span style=color:#e6db74>&#39;表名&#39;</span> <span style=color:#66d9ef>where</span> <span style=color:#e6db74>&#39;限制条件&#39;</span>  <span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span> <span style=color:#e6db74>&#39;分组依据&#39;</span> <span style=color:#66d9ef>having</span> <span style=color:#e6db74>&#39;过滤条件&#39;</span> <span style=color:#66d9ef>order</span> <span style=color:#66d9ef>by</span>  <span style=color:#66d9ef>limit</span> <span style=color:#e6db74>&#39;展示条数&#39;</span>
<span style=color:#960050;background-color:#1e0010>执行顺序</span>
<span style=color:#66d9ef>from</span>       <span style=color:#75715e>-- 查询
</span><span style=color:#75715e></span><span style=color:#66d9ef>where</span>      <span style=color:#75715e>-- 限制条件
</span><span style=color:#75715e></span><span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span>   <span style=color:#75715e>-- 分组
</span><span style=color:#75715e></span><span style=color:#66d9ef>having</span>     <span style=color:#75715e>-- 过滤条件
</span><span style=color:#75715e></span><span style=color:#66d9ef>order</span> <span style=color:#66d9ef>by</span>   <span style=color:#75715e>-- 排序
</span><span style=color:#75715e></span><span style=color:#66d9ef>limit</span>      <span style=color:#75715e>-- 展示条数
</span><span style=color:#75715e></span><span style=color:#66d9ef>distinct</span>   <span style=color:#75715e>-- 去重
</span><span style=color:#75715e></span><span style=color:#66d9ef>select</span>     <span style=color:#75715e>-- 查询的结果
</span><span style=color:#75715e></span><span style=color:#960050;background-color:#1e0010>正则：</span><span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>where</span> name regexp <span style=color:#e6db74>&#39;^j.*(n|y)$&#39;</span>;
<span style=color:#960050;background-color:#1e0010>集合查询：</span><span style=color:#66d9ef>max</span> <span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>min</span> <span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>avg</span> <span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>sum</span> <span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>count</span> <span style=color:#960050;background-color:#1e0010>、</span>group_concat <span style=color:#960050;background-color:#1e0010>。</span>
<span style=color:#960050;background-color:#1e0010>内连接：</span><span style=color:#66d9ef>inner</span> <span style=color:#66d9ef>join</span>
<span style=color:#960050;background-color:#1e0010>左连接：</span><span style=color:#66d9ef>left</span> <span style=color:#66d9ef>join</span>
<span style=color:#960050;background-color:#1e0010>右连接：</span><span style=color:#66d9ef>right</span> <span style=color:#66d9ef>join</span>
<span style=color:#960050;background-color:#1e0010>全连接：</span> <span style=color:#960050;background-color:#1e0010>左连接</span> <span style=color:#66d9ef>union</span> <span style=color:#960050;background-color:#1e0010>右连接</span>
<span style=color:#66d9ef>replace</span> <span style=color:#960050;background-color:#1e0010>替换</span>

<span style=color:#960050;background-color:#1e0010>拼接：</span>concat<span style=color:#960050;background-color:#1e0010>、</span>concat_ws<span style=color:#960050;background-color:#1e0010>、</span>group_concat
</code></pre></div><h2 id=常规设置操作>常规设置操作
<a class=header-anchor href=#%e5%b8%b8%e8%a7%84%e8%ae%be%e7%bd%ae%e6%93%8d%e4%bd%9c></a>
</h2><h3 id=1服务器设置远程访问>1.服务器设置远程访问
<a class=header-anchor href=#1%e6%9c%8d%e5%8a%a1%e5%99%a8%e8%ae%be%e7%bd%ae%e8%bf%9c%e7%a8%8b%e8%ae%bf%e9%97%ae></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>grant</span> <span style=color:#66d9ef>all</span> <span style=color:#66d9ef>privileges</span> <span style=color:#66d9ef>on</span> <span style=color:#f92672>*</span>.<span style=color:#f92672>*</span> <span style=color:#66d9ef>to</span> <span style=color:#e6db74>&#39;root&#39;</span><span style=color:#f92672>@</span><span style=color:#e6db74>&#39;%&#39;</span> identified <span style=color:#66d9ef>by</span> <span style=color:#e6db74>&#39;123456&#39;</span> <span style=color:#66d9ef>with</span> <span style=color:#66d9ef>grant</span> <span style=color:#66d9ef>option</span>;
</code></pre></div><h3 id=2常用数据类型>2.常用数据类型
<a class=header-anchor href=#2%e5%b8%b8%e7%94%a8%e6%95%b0%e6%8d%ae%e7%b1%bb%e5%9e%8b></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql>int        <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>整型，</span><span style=color:#ae81ff>4</span><span style=color:#960050;background-color:#1e0010>个字节</span>  <span style=color:#960050;background-color:#1e0010>一般不需要指定宽度，（</span><span style=color:#ae81ff>8</span><span style=color:#960050;background-color:#1e0010>）：只是显示为</span><span style=color:#ae81ff>8</span><span style=color:#960050;background-color:#1e0010>位，默认有负号设置无负号：</span> unsigned
double     <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>浮点型，例如</span>double(<span style=color:#ae81ff>5</span>,<span style=color:#ae81ff>2</span>),<span style=color:#960050;background-color:#1e0010>标识最多</span><span style=color:#ae81ff>5</span><span style=color:#960050;background-color:#1e0010>位，其中</span><span style=color:#ae81ff>2</span><span style=color:#960050;background-color:#1e0010>位为小数，即最大为</span><span style=color:#ae81ff>999</span>.<span style=color:#ae81ff>99</span>
varchar        <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>可变长度字符串类型。例如：</span>varchar(<span style=color:#ae81ff>10</span>) <span style=color:#e6db74>&#39;aaa&#39;</span> <span style=color:#960050;background-color:#1e0010>占</span><span style=color:#ae81ff>3</span><span style=color:#960050;background-color:#1e0010>位</span>
char       <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>固定长度字符串类型。例如：</span>char(<span style=color:#ae81ff>10</span>) <span style=color:#e6db74>&#39;aaa&#39;</span> <span style=color:#960050;background-color:#1e0010>占</span><span style=color:#ae81ff>10</span><span style=color:#960050;background-color:#1e0010>位</span>
text       <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>大文本字符串类型。</span>
blob       <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>字节类型。例如：</span>
datetime   <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>日期时间类型。例如：</span>datetime(yyyy<span style=color:#f92672>-</span>MM<span style=color:#f92672>-</span>dd hh:mm:ss)
date       <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>日期类型。例如：</span>date(yyyy:MM:dd)
time       <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>时间类型。例如：</span>time(hh:mm:ss)
<span style=color:#66d9ef>timestamp</span>  <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>时间戳类型。例如：</span><span style=color:#66d9ef>timestamp</span>(yyyy<span style=color:#f92672>-</span>MM<span style=color:#f92672>-</span>dd hh:mm:ss)  <span style=color:#960050;background-color:#1e0010>会自动赋值</span>
enum       <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>枚举</span> <span style=color:#960050;background-color:#1e0010>多选一</span> enum(<span style=color:#e6db74>&#39;male&#39;</span>,<span style=color:#e6db74>&#39;female&#39;</span>)<span style=color:#960050;background-color:#1e0010>，</span>default为默认值
<span style=color:#960050;background-color:#1e0010>例如：</span>sex enum(<span style=color:#e6db74>&#39;male&#39;</span>,<span style=color:#e6db74>&#39;female&#39;</span>) <span style=color:#66d9ef>not</span> <span style=color:#66d9ef>null</span> <span style=color:#66d9ef>default</span> <span style=color:#e6db74>&#39;male&#39;</span>
<span style=color:#66d9ef>set</span>            <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>集合</span> <span style=color:#960050;background-color:#1e0010>多选多</span>,<span style=color:#960050;background-color:#1e0010>可以选一个</span> <span style=color:#66d9ef>set</span><span style=color:#960050;background-color:#1e0010>（</span><span style=color:#e6db74>&#39;read&#39;</span>,<span style=color:#e6db74>&#39;DJ&#39;</span>,<span style=color:#e6db74>&#39;DBJ&#39;</span>,<span style=color:#e6db74>&#39;run&#39;</span>)
<span style=color:#960050;background-color:#1e0010>注：字符串类型和时间类型都要用单引号括起来，空值为</span><span style=color:#66d9ef>null</span>
</code></pre></div><h3 id=3查看数据列表>3.查看数据列表
<a class=header-anchor href=#3%e6%9f%a5%e7%9c%8b%e6%95%b0%e6%8d%ae%e5%88%97%e8%a1%a8></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>show</span> databases; <span style=color:#75715e>-- 查看所有数据库
</span><span style=color:#75715e></span><span style=color:#66d9ef>show</span> <span style=color:#66d9ef>create</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>表名</span>; <span style=color:#75715e>-- 查看表的创建细节
</span><span style=color:#75715e></span><span style=color:#66d9ef>desc</span> <span style=color:#960050;background-color:#1e0010>表名</span>; <span style=color:#75715e>-- 查看表结构
</span></code></pre></div><h3 id=4进入数据库>4.进入数据库
<a class=header-anchor href=#4%e8%bf%9b%e5%85%a5%e6%95%b0%e6%8d%ae%e5%ba%93></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span>use <span style=color:#960050;background-color:#1e0010>数据名</span>
use Python_7
</code></pre></div><h3 id=5创建数据库>5.创建数据库
<a class=header-anchor href=#5%e5%88%9b%e5%bb%ba%e6%95%b0%e6%8d%ae%e5%ba%93></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span><span style=color:#66d9ef>CREATE</span> <span style=color:#66d9ef>DATABASE</span> <span style=color:#960050;background-color:#1e0010>数据库名</span>
<span style=color:#66d9ef>CREATE</span> <span style=color:#66d9ef>DATABASE</span> Python_7;
<span style=color:#66d9ef>CREATE</span> <span style=color:#66d9ef>DATABASE</span> pyrhon_7 charset utf8

<span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>修改数据库编码</span>
<span style=color:#66d9ef>alter</span> <span style=color:#66d9ef>database</span> db1 charset gbk;
</code></pre></div><h3 id=6删除数据库>6.删除数据库
<a class=header-anchor href=#6%e5%88%a0%e9%99%a4%e6%95%b0%e6%8d%ae%e5%ba%93></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span><span style=color:#66d9ef>drop</span> <span style=color:#66d9ef>database</span> <span style=color:#960050;background-color:#1e0010>需要删除的数据库名</span>
<span style=color:#66d9ef>drop</span> <span style=color:#66d9ef>database</span> Python_7;
</code></pre></div><h3 id=7查看表>7.查看表
<a class=header-anchor href=#7%e6%9f%a5%e7%9c%8b%e8%a1%a8></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>select</span> <span style=color:#66d9ef>database</span>();  <span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>查看当前所在的库</span>
<span style=color:#66d9ef>show</span> tables; <span style=color:#75715e>-- 查看数据库中所有表
</span><span style=color:#75715e></span><span style=color:#66d9ef>desc</span> <span style=color:#960050;background-color:#1e0010>表名</span>; <span style=color:#75715e>-- 查看表结构
</span><span style=color:#75715e></span><span style=color:#66d9ef>show</span> <span style=color:#66d9ef>create</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>表名</span>; <span style=color:#75715e>-- 查看表的创建细节
</span></code></pre></div><h3 id=8创建表>8.创建表
<a class=header-anchor href=#8%e5%88%9b%e5%bb%ba%e8%a1%a8></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>创建新表</span>
<span style=color:#f92672>#</span> <span style=color:#66d9ef>create</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>新建数据表名（</span>
<span style=color:#f92672>#</span>      <span style=color:#960050;background-color:#1e0010>字段名</span> <span style=color:#960050;background-color:#1e0010>类型</span>(<span style=color:#960050;background-color:#1e0010>长度</span>) [<span style=color:#960050;background-color:#1e0010>约束</span>(<span style=color:#960050;background-color:#1e0010>具体见</span><span style=color:#ae81ff>1</span>)],
<span style=color:#f92672>#</span>      <span style=color:#960050;background-color:#1e0010>字段名</span> <span style=color:#960050;background-color:#1e0010>类型</span>(<span style=color:#960050;background-color:#1e0010>长度</span>) [<span style=color:#960050;background-color:#1e0010>约束</span>(<span style=color:#960050;background-color:#1e0010>具体见</span><span style=color:#ae81ff>1</span>)]
<span style=color:#f92672>#</span>  );
<span style=color:#66d9ef>create</span> <span style=color:#66d9ef>table</span> <span style=color:#66d9ef>class</span>(
      id INT AUTO_INCREMENT,
  name varchar(<span style=color:#ae81ff>32</span>) <span style=color:#66d9ef>UNIQUE</span>,
  age varchar(<span style=color:#ae81ff>32</span>) <span style=color:#66d9ef>NOT</span> <span style=color:#66d9ef>NULL</span>
);

<span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>需要注意</span>


<span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>根据已有的表创建新表</span>
<span style=color:#66d9ef>create</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>新表</span> <span style=color:#66d9ef>like</span> <span style=color:#960050;background-color:#1e0010>旧表</span>  <span style=color:#75715e>-- 使用旧表创建新表
</span><span style=color:#75715e></span><span style=color:#66d9ef>create</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>新表</span> <span style=color:#66d9ef>as</span> <span style=color:#66d9ef>select</span> <span style=color:#960050;background-color:#1e0010>字段</span><span style=color:#ae81ff>1</span> <span style=color:#960050;background-color:#1e0010>字段</span><span style=color:#ae81ff>2</span>... <span style=color:#66d9ef>from</span> definition <span style=color:#66d9ef>only</span> <span style=color:#75715e>-- 使用自定义值去新建表
</span></code></pre></div><h3 id=9删除表>9.删除表
<a class=header-anchor href=#9%e5%88%a0%e9%99%a4%e8%a1%a8></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span><span style=color:#66d9ef>drop</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>数据库表名</span>
<span style=color:#66d9ef>drop</span> <span style=color:#66d9ef>table</span> Python
</code></pre></div><h3 id=10修改表>10.修改表
<a class=header-anchor href=#10%e4%bf%ae%e6%94%b9%e8%a1%a8></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>alter</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>add</span> <span style=color:#960050;background-color:#1e0010>字段名</span> <span style=color:#960050;background-color:#1e0010>类型（长度）</span> [<span style=color:#960050;background-color:#1e0010>约束</span>]; <span style=color:#75715e>-- 添加列
</span><span style=color:#75715e></span><span style=color:#66d9ef>alter</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>modify</span> <span style=color:#960050;background-color:#1e0010>字段名</span> <span style=color:#960050;background-color:#1e0010>类型（长度）</span> [<span style=color:#960050;background-color:#1e0010>约束</span>]; <span style=color:#75715e>-- 修改列的类型长度及约束
</span><span style=color:#75715e></span><span style=color:#66d9ef>alter</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>表名</span> change <span style=color:#960050;background-color:#1e0010>旧字段名</span> <span style=color:#960050;background-color:#1e0010>新字段名</span> <span style=color:#960050;background-color:#1e0010>类型（长度）</span> [<span style=color:#960050;background-color:#1e0010>约束</span>]; <span style=color:#75715e>-- 修改列表名
</span><span style=color:#75715e></span><span style=color:#66d9ef>alter</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>drop</span> <span style=color:#960050;background-color:#1e0010>字段名</span>; <span style=color:#75715e>-- 删除列
</span><span style=color:#75715e></span><span style=color:#66d9ef>alter</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>表名</span> character <span style=color:#66d9ef>set</span> <span style=color:#960050;background-color:#1e0010>字符集</span>; <span style=color:#75715e>-- 修改表的字符集
</span><span style=color:#75715e></span><span style=color:#66d9ef>rename</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>to</span> <span style=color:#960050;background-color:#1e0010>新表名</span>; <span style=color:#75715e>-- 修改表名
</span></code></pre></div><h3 id=11增加数据>11.增加数据
<a class=header-anchor href=#11%e5%a2%9e%e5%8a%a0%e6%95%b0%e6%8d%ae></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>insert</span> <span style=color:#66d9ef>into</span> <span style=color:#960050;background-color:#1e0010>表</span>(<span style=color:#960050;background-color:#1e0010>字段名</span><span style=color:#ae81ff>1</span><span style=color:#960050;background-color:#1e0010>，字段名</span><span style=color:#ae81ff>2</span>..) <span style=color:#66d9ef>values</span>(<span style=color:#960050;background-color:#1e0010>值</span><span style=color:#ae81ff>1</span><span style=color:#960050;background-color:#1e0010>，值</span><span style=color:#ae81ff>2</span>..);<span style=color:#75715e>-- 向表中插入某些列
</span><span style=color:#75715e></span><span style=color:#66d9ef>insert</span> <span style=color:#66d9ef>into</span> <span style=color:#960050;background-color:#1e0010>表</span> <span style=color:#66d9ef>values</span>(<span style=color:#960050;background-color:#1e0010>值</span><span style=color:#ae81ff>1</span><span style=color:#960050;background-color:#1e0010>，值</span><span style=color:#ae81ff>2</span><span style=color:#960050;background-color:#1e0010>，值</span><span style=color:#ae81ff>3</span>..); <span style=color:#75715e>-- 向表中插入所有列
</span></code></pre></div><h3 id=12修改数据>12.修改数据
<a class=header-anchor href=#12%e4%bf%ae%e6%94%b9%e6%95%b0%e6%8d%ae></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>update</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>set</span> <span style=color:#960050;background-color:#1e0010>字段名</span><span style=color:#f92672>=</span><span style=color:#960050;background-color:#1e0010>值</span>,<span style=color:#960050;background-color:#1e0010>字段名</span><span style=color:#f92672>=</span><span style=color:#960050;background-color:#1e0010>值</span>...; <span style=color:#75715e>-- 这个会修改所有的数据，把一列的值都变了
</span><span style=color:#75715e></span><span style=color:#66d9ef>update</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>set</span> <span style=color:#960050;background-color:#1e0010>字段名</span><span style=color:#f92672>=</span><span style=color:#960050;background-color:#1e0010>值</span>,<span style=color:#960050;background-color:#1e0010>字段名</span><span style=color:#f92672>=</span><span style=color:#960050;background-color:#1e0010>值</span>... <span style=color:#66d9ef>where</span> <span style=color:#960050;background-color:#1e0010>条件</span>; <span style=color:#75715e>-- 只改符合where条件的行
</span></code></pre></div><h3 id=13删除数据>13.删除数据
<a class=header-anchor href=#13%e5%88%a0%e9%99%a4%e6%95%b0%e6%8d%ae></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>delete</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span>  <span style=color:#75715e>-- 删除表中所有记录
</span><span style=color:#75715e></span><span style=color:#66d9ef>delete</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>where</span> <span style=color:#960050;background-color:#1e0010>条件</span> <span style=color:#75715e>-- 删除符合 where条件的数据
</span><span style=color:#75715e></span><span style=color:#66d9ef>truncate</span> <span style=color:#66d9ef>table</span> <span style=color:#960050;background-color:#1e0010>表名</span>; <span style=color:#75715e>-- 把表直接drop掉，重新建表，auto_increment将置为零。删除的数据不能找回。执行速度比delete快
</span></code></pre></div><h3 id=14数据的简单查询>14.数据的简单查询
<a class=header-anchor href=#14%e6%95%b0%e6%8d%ae%e7%9a%84%e7%ae%80%e5%8d%95%e6%9f%a5%e8%af%a2></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span>; <span style=color:#75715e>-- 查询所有列
</span><span style=color:#75715e></span><span style=color:#66d9ef>select</span> <span style=color:#960050;background-color:#1e0010>字段名</span><span style=color:#ae81ff>1</span>,<span style=color:#960050;background-color:#1e0010>字段名</span><span style=color:#ae81ff>2</span>,<span style=color:#960050;background-color:#1e0010>字段名</span><span style=color:#ae81ff>3</span>.. <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span>; <span style=color:#75715e>-- 查询指定列
</span></code></pre></div><h3 id=15几个简单的基本的sql语句>15.几个简单的基本的sql语句
<a class=header-anchor href=#15%e5%87%a0%e4%b8%aa%e7%ae%80%e5%8d%95%e7%9a%84%e5%9f%ba%e6%9c%ac%e7%9a%84sql%e8%af%ad%e5%8f%a5></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>where</span> <span style=color:#960050;background-color:#1e0010>范围</span>                           <span style=color:#75715e>-- 选择查询
</span><span style=color:#75715e></span><span style=color:#66d9ef>insert</span> <span style=color:#66d9ef>into</span> <span style=color:#960050;background-color:#1e0010>表名</span>(field1,field2) <span style=color:#66d9ef>values</span>(value1,value2)   <span style=color:#75715e>-- 插入
</span><span style=color:#75715e></span><span style=color:#66d9ef>delete</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>where</span> <span style=color:#960050;background-color:#1e0010>范围</span>                             <span style=color:#75715e>-- 删除
</span><span style=color:#75715e></span><span style=color:#66d9ef>update</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>set</span> field1<span style=color:#f92672>=</span>value1 <span style=color:#66d9ef>where</span> <span style=color:#960050;background-color:#1e0010>范围</span>                <span style=color:#75715e>-- 更新
</span><span style=color:#75715e></span><span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>where</span> field1 <span style=color:#66d9ef>like</span> <span style=color:#960050;background-color:#1e0010>’</span><span style=color:#f92672>%</span>value1<span style=color:#f92672>%</span><span style=color:#960050;background-color:#1e0010>’</span>            <span style=color:#75715e>-- 查找
</span><span style=color:#75715e></span><span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>order</span> <span style=color:#66d9ef>by</span> field1,field2 [<span style=color:#66d9ef>desc</span>]        <span style=color:#75715e>-- 排序：
</span><span style=color:#75715e></span><span style=color:#66d9ef>select</span> <span style=color:#66d9ef>count</span> <span style=color:#66d9ef>as</span> <span style=color:#960050;background-color:#1e0010>需要统计总数的字段名</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span>             <span style=color:#75715e>-- 总数
</span><span style=color:#75715e></span><span style=color:#66d9ef>select</span> <span style=color:#66d9ef>sum</span>(field1) <span style=color:#66d9ef>as</span> sumvalue <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span>               <span style=color:#75715e>-- 求和
</span><span style=color:#75715e></span><span style=color:#66d9ef>select</span> <span style=color:#66d9ef>avg</span>(field1) <span style=color:#66d9ef>as</span> avgvalue <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span>               <span style=color:#75715e>-- 平均
</span><span style=color:#75715e></span><span style=color:#66d9ef>select</span> <span style=color:#66d9ef>max</span>(field1) <span style=color:#66d9ef>as</span> <span style=color:#66d9ef>maxvalue</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span>               <span style=color:#75715e>-- 最大
</span><span style=color:#75715e></span><span style=color:#66d9ef>select</span> <span style=color:#66d9ef>min</span>(field1) <span style=color:#66d9ef>as</span> <span style=color:#66d9ef>minvalue</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span>               <span style=color:#75715e>-- 最小
</span></code></pre></div><h3 id=16like-的用法>16.like 的用法
<a class=header-anchor href=#16like-%e7%9a%84%e7%94%a8%e6%b3%95></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql>A:<span style=color:#f92672>%</span> <span style=color:#960050;background-color:#1e0010>包含零个或多个字符的任意字符串：</span>
<span style=color:#ae81ff>1</span><span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>like</span><span style=color:#e6db74>&#39;Mc%&#39;</span> <span style=color:#960050;background-color:#1e0010>将搜索以字母</span> Mc <span style=color:#960050;background-color:#1e0010>开头的所有字符串（如</span> McBadden<span style=color:#960050;background-color:#1e0010>）。</span>
<span style=color:#ae81ff>2</span><span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>like</span><span style=color:#e6db74>&#39;%inger&#39;</span> <span style=color:#960050;background-color:#1e0010>将搜索以字母</span> inger <span style=color:#960050;background-color:#1e0010>结尾的所有字符串（如</span> Ringer<span style=color:#960050;background-color:#1e0010>、</span>Stringer<span style=color:#960050;background-color:#1e0010>）。</span>
<span style=color:#ae81ff>3</span><span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>like</span><span style=color:#e6db74>&#39;%en%&#39;</span> <span style=color:#960050;background-color:#1e0010>将搜索在任何位置包含字母</span> en <span style=color:#960050;background-color:#1e0010>的所有字符串（如</span> Bennet<span style=color:#960050;background-color:#1e0010>、</span>Green<span style=color:#960050;background-color:#1e0010>、</span>McBadden<span style=color:#960050;background-color:#1e0010>）。</span>

B:_<span style=color:#960050;background-color:#1e0010>（下划线）</span> <span style=color:#960050;background-color:#1e0010>任何单个字符：</span>
  <span style=color:#66d9ef>like</span><span style=color:#e6db74>&#39;_heryl&#39;</span> <span style=color:#960050;background-color:#1e0010>将搜索以字母</span> heryl <span style=color:#960050;background-color:#1e0010>结尾的所有六个字母的名称（如</span> Cheryl<span style=color:#960050;background-color:#1e0010>、</span>Sheryl<span style=color:#960050;background-color:#1e0010>）。</span>

<span style=color:#66d9ef>C</span><span style=color:#960050;background-color:#1e0010>：</span>[ ] <span style=color:#960050;background-color:#1e0010>指定范围</span> ([a<span style=color:#f92672>-</span>f]) <span style=color:#960050;background-color:#1e0010>或集合</span> ([abcdef]) <span style=color:#960050;background-color:#1e0010>中的任何单个字符：</span>
  <span style=color:#ae81ff>1</span><span style=color:#960050;background-color:#1e0010>，</span><span style=color:#66d9ef>like</span><span style=color:#e6db74>&#39;[CK]ars[eo]n&#39;</span> <span style=color:#960050;background-color:#1e0010>将搜索下列字符串：</span>Carsen<span style=color:#960050;background-color:#1e0010>、</span>Karsen<span style=color:#960050;background-color:#1e0010>、</span>Carson <span style=color:#960050;background-color:#1e0010>和</span> Karson<span style=color:#960050;background-color:#1e0010>（如</span> Carson<span style=color:#960050;background-color:#1e0010>）。</span>
  <span style=color:#ae81ff>2</span><span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>like</span><span style=color:#e6db74>&#39;[M-Z]inger&#39;</span> <span style=color:#960050;background-color:#1e0010>将搜索以字符串</span> inger <span style=color:#960050;background-color:#1e0010>结尾、以从</span> M <span style=color:#960050;background-color:#1e0010>到</span> Z <span style=color:#960050;background-color:#1e0010>的任何单个字母开头的所有名称（如</span> Ringer<span style=color:#960050;background-color:#1e0010>）。</span>

D<span style=color:#960050;background-color:#1e0010>：</span>[<span style=color:#f92672>^</span>] <span style=color:#960050;background-color:#1e0010>不属于指定范围</span> ([a<span style=color:#f92672>-</span>f]) <span style=color:#960050;background-color:#1e0010>或集合</span> ([abcdef]) <span style=color:#960050;background-color:#1e0010>的任何单个字符：</span>
  <span style=color:#66d9ef>like</span><span style=color:#e6db74>&#39;M[^c]%&#39;</span> <span style=color:#960050;background-color:#1e0010>将搜索以字母</span> M <span style=color:#960050;background-color:#1e0010>开头，并且第二个字母不是</span> <span style=color:#66d9ef>c</span> <span style=color:#960050;background-color:#1e0010>的所有名称（如</span>MacFeather<span style=color:#960050;background-color:#1e0010>）。</span>

E<span style=color:#960050;background-color:#1e0010>：</span><span style=color:#f92672>*</span> <span style=color:#960050;background-color:#1e0010>它同于</span>DOS命令中的通配符<span style=color:#960050;background-color:#1e0010>，代表多个字符：</span>
  <span style=color:#66d9ef>c</span><span style=color:#f92672>*</span>c代表cc,cBc,cbc,cabdfec等多个字符<span style=color:#960050;background-color:#1e0010>。</span>

F<span style=color:#960050;background-color:#1e0010>：？同于</span>DOS命令中的<span style=color:#960050;background-color:#1e0010>？通配符，代表单个字符</span> :
  b<span style=color:#f92672>?</span>b代表brb,bFb等

<span style=color:#66d9ef>G</span><span style=color:#960050;background-color:#1e0010>：</span><span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>大致同上，不同的是代只能代表单个数字。</span>k<span style=color:#f92672>#</span>k代表k1k,k8k,k0k <span style=color:#960050;background-color:#1e0010>。</span>

<span style=color:#960050;background-color:#1e0010>下面我们来举例说明一下：</span>

<span style=color:#960050;background-color:#1e0010>例</span><span style=color:#ae81ff>1</span><span style=color:#960050;background-color:#1e0010>，查询</span>name字段中包含有<span style=color:#960050;background-color:#1e0010>“明”字的。</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> table1 <span style=color:#66d9ef>where</span> name <span style=color:#66d9ef>like</span> <span style=color:#e6db74>&#39;%明%&#39;</span>

<span style=color:#960050;background-color:#1e0010>例</span><span style=color:#ae81ff>2</span><span style=color:#960050;background-color:#1e0010>，查询</span>name字段中以<span style=color:#960050;background-color:#1e0010>“李”字开头。</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> table1 <span style=color:#66d9ef>where</span> name <span style=color:#66d9ef>like</span> <span style=color:#e6db74>&#39;李*&#39;</span>

<span style=color:#960050;background-color:#1e0010>例</span><span style=color:#ae81ff>3</span><span style=color:#960050;background-color:#1e0010>，查询</span>name字段中含有数字的<span style=color:#960050;background-color:#1e0010>。</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> table1 <span style=color:#66d9ef>where</span> name <span style=color:#66d9ef>like</span> <span style=color:#e6db74>&#39;%[0-9]%&#39;</span>

<span style=color:#960050;background-color:#1e0010>例</span><span style=color:#ae81ff>4</span><span style=color:#960050;background-color:#1e0010>，查询</span>name字段中含有小写字母的<span style=color:#960050;background-color:#1e0010>。</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> table1 <span style=color:#66d9ef>where</span> name <span style=color:#66d9ef>like</span> <span style=color:#e6db74>&#39;%[a-z]%&#39;</span>

<span style=color:#960050;background-color:#1e0010>例</span><span style=color:#ae81ff>5</span><span style=color:#960050;background-color:#1e0010>，查询</span>name字段中不含有数字的<span style=color:#960050;background-color:#1e0010>。</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> table1 <span style=color:#66d9ef>where</span> name <span style=color:#66d9ef>like</span> <span style=color:#e6db74>&#39;%[!0-9]%&#39;</span>

<span style=color:#960050;background-color:#1e0010>以上例子能列出什么值来显而易见。但在这里，我们着重要说明的是通配符“</span><span style=color:#f92672>*</span><span style=color:#960050;background-color:#1e0010>”与“</span><span style=color:#f92672>%</span><span style=color:#960050;background-color:#1e0010>”的区别。</span>

<span style=color:#960050;background-color:#1e0010>很多朋友会问，为什么我在以上查询时有个别的表示所有字符的时候用</span><span style=color:#e6db74>&#34;%&#34;</span><span style=color:#960050;background-color:#1e0010>而不用“</span><span style=color:#f92672>*</span><span style=color:#960050;background-color:#1e0010>”？先看看下面的例子能分别出现什么结果：</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> table1 <span style=color:#66d9ef>where</span> name <span style=color:#66d9ef>like</span> <span style=color:#e6db74>&#39;*明*&#39;</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> table1 <span style=color:#66d9ef>where</span> name <span style=color:#66d9ef>like</span> <span style=color:#e6db74>&#39;%明%&#39;</span>
<span style=color:#960050;background-color:#1e0010>大家会看到，前一条语句列出来的是所有的记录，而后一条记录列出来的是</span>name字段中含有<span style=color:#960050;background-color:#1e0010>“明”的记录，所以说，当我们作字符型字段包含一个子串的查询时最好采用“</span><span style=color:#f92672>%</span><span style=color:#960050;background-color:#1e0010>”而不用“</span><span style=color:#f92672>*</span><span style=color:#960050;background-color:#1e0010>”</span>,<span style=color:#960050;background-color:#1e0010>用“</span><span style=color:#f92672>*</span><span style=color:#960050;background-color:#1e0010>”的时候只在开头或者只在结尾时，而不能两端全由“</span><span style=color:#f92672>*</span><span style=color:#960050;background-color:#1e0010>”代替任意字符的情况下。</span>
</code></pre></div><h2 id=单表查询查询>单表查询查询
<a class=header-anchor href=#%e5%8d%95%e8%a1%a8%e6%9f%a5%e8%af%a2%e6%9f%a5%e8%af%a2></a>
</h2><h3 id=1综合使用>1.综合使用
<a class=header-anchor href=#1%e7%bb%bc%e5%90%88%e4%bd%bf%e7%94%a8></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span><span style=color:#75715e>----综合使用
</span><span style=color:#75715e></span><span style=color:#960050;background-color:#1e0010>书写顺序</span>
<span style=color:#66d9ef>select</span> <span style=color:#66d9ef>distinct</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> <span style=color:#e6db74>&#39;表名&#39;</span> <span style=color:#66d9ef>where</span> <span style=color:#e6db74>&#39;限制条件&#39;</span>  <span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span> <span style=color:#e6db74>&#39;分组依据&#39;</span> <span style=color:#66d9ef>having</span> <span style=color:#e6db74>&#39;过滤条件&#39;</span> <span style=color:#66d9ef>order</span> <span style=color:#66d9ef>by</span>  <span style=color:#66d9ef>limit</span> <span style=color:#e6db74>&#39;展示条数&#39;</span>
<span style=color:#960050;background-color:#1e0010>执行顺序</span>
<span style=color:#66d9ef>from</span>       <span style=color:#75715e>-- 查询
</span><span style=color:#75715e></span><span style=color:#66d9ef>where</span>      <span style=color:#75715e>-- 限制条件
</span><span style=color:#75715e></span><span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span>   <span style=color:#75715e>-- 分组
</span><span style=color:#75715e></span><span style=color:#66d9ef>having</span>     <span style=color:#75715e>-- 过滤条件
</span><span style=color:#75715e></span><span style=color:#66d9ef>order</span> <span style=color:#66d9ef>by</span>   <span style=color:#75715e>-- 排序
</span><span style=color:#75715e></span><span style=color:#66d9ef>limit</span>      <span style=color:#75715e>-- 展示条数
</span><span style=color:#75715e></span><span style=color:#66d9ef>distinct</span>   <span style=color:#75715e>-- 去重
</span><span style=color:#75715e></span><span style=color:#66d9ef>select</span>     <span style=color:#75715e>-- 查询的结果
</span><span style=color:#75715e></span>
<span style=color:#960050;background-color:#1e0010>正则：</span><span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>where</span> name regexp <span style=color:#e6db74>&#39;^j.*(n|y)$&#39;</span>;
<span style=color:#960050;background-color:#1e0010>集合查询：</span><span style=color:#66d9ef>max</span> <span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>min</span> <span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>avg</span> <span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>sum</span> <span style=color:#960050;background-color:#1e0010>、</span><span style=color:#66d9ef>count</span> <span style=color:#960050;background-color:#1e0010>、</span>group_concat <span style=color:#960050;background-color:#1e0010>。</span>
<span style=color:#960050;background-color:#1e0010>拼接：</span>concat<span style=color:#960050;background-color:#1e0010>、</span>concat_ws<span style=color:#960050;background-color:#1e0010>、</span>group_concat
<span style=color:#960050;background-color:#1e0010>内连接：</span><span style=color:#66d9ef>inner</span> <span style=color:#66d9ef>join</span>
<span style=color:#960050;background-color:#1e0010>左连接：</span><span style=color:#66d9ef>left</span> <span style=color:#66d9ef>join</span>
<span style=color:#960050;background-color:#1e0010>右连接：</span><span style=color:#66d9ef>right</span> <span style=color:#66d9ef>join</span>
<span style=color:#960050;background-color:#1e0010>全连接：</span> <span style=color:#960050;background-color:#1e0010>左连接</span> <span style=color:#66d9ef>union</span> <span style=color:#960050;background-color:#1e0010>右连接</span>
</code></pre></div><h3 id=2where-查询>2.where 查询
<a class=header-anchor href=#2where-%e6%9f%a5%e8%af%a2></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span> <span style=color:#66d9ef>between</span> <span style=color:#960050;background-color:#1e0010>在</span>...<span style=color:#960050;background-color:#1e0010>之间</span>
<span style=color:#66d9ef>select</span> id,name <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>where</span> id <span style=color:#f92672>&gt;=</span> <span style=color:#ae81ff>3</span> <span style=color:#66d9ef>and</span> id <span style=color:#f92672>&lt;=</span> <span style=color:#ae81ff>6</span>;
<span style=color:#960050;background-color:#1e0010>相当于：</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span>  <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>where</span> id <span style=color:#66d9ef>between</span> <span style=color:#ae81ff>3</span> <span style=color:#66d9ef>and</span> <span style=color:#ae81ff>6</span>;

<span style=color:#f92672>#</span> <span style=color:#66d9ef>or</span> <span style=color:#960050;background-color:#1e0010>或者</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span>  <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>where</span> id <span style=color:#f92672>&gt;=</span> <span style=color:#ae81ff>3</span> <span style=color:#66d9ef>or</span> id <span style=color:#f92672>&lt;=</span> <span style=color:#ae81ff>6</span>;

<span style=color:#f92672>#</span> <span style=color:#66d9ef>in</span><span style=color:#960050;background-color:#1e0010>，后面可以跟多个值</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>where</span> <span style=color:#960050;background-color:#1e0010>字段名</span> <span style=color:#66d9ef>in</span> (<span style=color:#960050;background-color:#1e0010>条件</span><span style=color:#ae81ff>1</span>,<span style=color:#960050;background-color:#1e0010>条件</span><span style=color:#ae81ff>2</span>,<span style=color:#960050;background-color:#1e0010>条件三</span>);

<span style=color:#f92672>#</span> <span style=color:#66d9ef>like</span> <span style=color:#960050;background-color:#1e0010>（见上</span><span style=color:#ae81ff>18</span><span style=color:#960050;background-color:#1e0010>）</span>
<span style=color:#f92672>#</span> char<span style=color:#960050;background-color:#1e0010>——</span><span style=color:#66d9ef>length</span>()  <span style=color:#960050;background-color:#1e0010>取字符长度</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span> <span style=color:#66d9ef>where</span> <span style=color:#66d9ef>char_length</span>(<span style=color:#960050;background-color:#1e0010>需要获取长度的字段名</span>) <span style=color:#f92672>=</span> <span style=color:#ae81ff>4</span>;


<span style=color:#66d9ef>not</span> <span style=color:#960050;background-color:#1e0010>配合使用</span>
<span style=color:#960050;background-color:#1e0010>注意：判断空不能用</span> <span style=color:#f92672>=</span> <span style=color:#960050;background-color:#1e0010>，只能用</span> <span style=color:#66d9ef>is</span>
<span style=color:#ae81ff>2</span>.<span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span> <span style=color:#960050;background-color:#1e0010>分组</span>
<span style=color:#66d9ef>select</span> <span style=color:#960050;background-color:#1e0010>查询字段</span><span style=color:#ae81ff>1</span>,<span style=color:#960050;background-color:#1e0010>查询字段</span><span style=color:#ae81ff>2</span>,... <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span>
      <span style=color:#66d9ef>where</span> <span style=color:#960050;background-color:#1e0010>过滤条件</span>
      <span style=color:#66d9ef>group</span> by分组依据  <span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>分组后取出的是每个组的第一条数据</span>
</code></pre></div><h3 id=2聚合查询-以组为单位统计组内数据聚合查询聚集到一起合成为一个结果>2.聚合查询 ：以组为单位统计组内数据&#187;>聚合查询(聚集到一起合成为一个结果)
<a class=header-anchor href=#2%e8%81%9a%e5%90%88%e6%9f%a5%e8%af%a2-%e4%bb%a5%e7%bb%84%e4%b8%ba%e5%8d%95%e4%bd%8d%e7%bb%9f%e8%ae%a1%e7%bb%84%e5%86%85%e6%95%b0%e6%8d%ae%e8%81%9a%e5%90%88%e6%9f%a5%e8%af%a2%e8%81%9a%e9%9b%86%e5%88%b0%e4%b8%80%e8%b5%b7%e5%90%88%e6%88%90%e4%b8%ba%e4%b8%80%e4%b8%aa%e7%bb%93%e6%9e%9c></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span> <span style=color:#66d9ef>max</span> <span style=color:#960050;background-color:#1e0010>最大值</span>
<span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>每个部门的最高工资</span>
<span style=color:#66d9ef>select</span> post,<span style=color:#66d9ef>max</span>(salary) <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span> post;

<span style=color:#f92672>#</span> <span style=color:#66d9ef>min</span> <span style=color:#960050;background-color:#1e0010>最小值</span>
<span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>每个部门的最低工资</span>
<span style=color:#66d9ef>select</span> post,<span style=color:#66d9ef>min</span>(salary) <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span> post;

<span style=color:#f92672>#</span> <span style=color:#66d9ef>avg</span> <span style=color:#960050;background-color:#1e0010>平均值</span>
<span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>每个部门的平均工资</span>
<span style=color:#66d9ef>select</span> post,<span style=color:#66d9ef>avg</span>(salary) <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span> post;
<span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>每个部门的工资总和</span>

<span style=color:#f92672>#</span> <span style=color:#66d9ef>sum</span> <span style=color:#960050;background-color:#1e0010>求和</span>
<span style=color:#66d9ef>select</span> post,<span style=color:#66d9ef>sum</span>(salary) <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span> post;

<span style=color:#f92672>#</span> <span style=color:#66d9ef>count</span><span style=color:#960050;background-color:#1e0010>（需要计数字段）</span> <span style=color:#960050;background-color:#1e0010>计数</span>
<span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>每个部门的人数</span>
<span style=color:#66d9ef>select</span> post,<span style=color:#66d9ef>count</span>(id) <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span> post;

<span style=color:#f92672>#</span> group_concat(<span style=color:#960050;background-color:#1e0010>需要分组后的字段</span>) <span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>不仅可以用来显示除分组外字段还有拼接字符串的作用</span>
  <span style=color:#66d9ef>select</span> post,group_concat(name) <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span> post;
  <span style=color:#75715e>-- post：分组字段，name 需要分组后显示的字段
</span><span style=color:#75715e></span><span style=color:#960050;background-color:#1e0010>拼接：</span>
  concat<span style=color:#960050;background-color:#1e0010>（不分组时用）拼接字符串达到更好的显示效果</span> as语法使用
  <span style=color:#960050;background-color:#1e0010>举例：</span>
      <span style=color:#66d9ef>select</span> concat(<span style=color:#e6db74>&#34;NAME: &#34;</span>,name) <span style=color:#66d9ef>as</span> <span style=color:#960050;background-color:#1e0010>姓名</span> <span style=color:#66d9ef>from</span> emp;
  concat_ws: <span style=color:#960050;background-color:#1e0010>如果拼接的符号是统一的可以用</span>
  <span style=color:#960050;background-color:#1e0010>举例：</span>
      <span style=color:#66d9ef>select</span> concat_ws(<span style=color:#e6db74>&#39;:&#39;</span>,name,age,sex) <span style=color:#66d9ef>as</span> info <span style=color:#66d9ef>from</span> emp;
  group_concat<span style=color:#960050;background-color:#1e0010>：</span>
  <span style=color:#960050;background-color:#1e0010>举例：</span>
      <span style=color:#66d9ef>select</span> post,group_concat(name,<span style=color:#e6db74>&#39;DSB&#39;</span>) <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>group</span> <span style=color:#66d9ef>by</span> post;

<span style=color:#960050;background-color:#1e0010>补充：</span>as语法 <span style=color:#960050;background-color:#1e0010>起别名</span>
<span style=color:#66d9ef>select</span> name <span style=color:#66d9ef>as</span> <span style=color:#960050;background-color:#1e0010>姓名</span>,salary <span style=color:#66d9ef>as</span> <span style=color:#960050;background-color:#1e0010>薪资</span> <span style=color:#66d9ef>from</span> emp;
</code></pre></div><h3 id=3having-过滤查询>3.having 过滤查询
<a class=header-anchor href=#3having-%e8%bf%87%e6%bb%a4%e6%9f%a5%e8%af%a2></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span> having的语法格式与where一致<span style=color:#960050;background-color:#1e0010>，只不过</span>having是在分组之后进行的过滤<span style=color:#960050;background-color:#1e0010>，即</span>where虽然不能用聚合函数<span style=color:#960050;background-color:#1e0010>，但是</span>having可以<span style=color:#960050;background-color:#1e0010>！</span>

<span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>用法</span>
<span style=color:#66d9ef>select</span> <span style=color:#960050;background-color:#1e0010>查询字段</span><span style=color:#ae81ff>1</span>,<span style=color:#960050;background-color:#1e0010>查询字段</span><span style=color:#ae81ff>2</span>,... <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span>
      <span style=color:#66d9ef>where</span> <span style=color:#960050;background-color:#1e0010>过滤条件</span><span style=color:#ae81ff>1</span>
      <span style=color:#66d9ef>group</span> by分组依据
<span style=color:#66d9ef>having</span> <span style=color:#66d9ef>avg</span>(<span style=color:#960050;background-color:#1e0010>过滤条件</span><span style=color:#ae81ff>2</span>) <span style=color:#f92672>&gt;</span> <span style=color:#ae81ff>10000</span>;
</code></pre></div><h3 id=4distinct-去重>4.distinct 去重
<a class=header-anchor href=#4distinct-%e5%8e%bb%e9%87%8d></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>对有重复的展示数据进行去重操作</span>
<span style=color:#66d9ef>select</span> <span style=color:#66d9ef>distinct</span> <span style=color:#960050;background-color:#1e0010>需取重字段</span> <span style=color:#66d9ef>from</span> <span style=color:#960050;background-color:#1e0010>表名</span>;
</code></pre></div><h3 id=5order-by-排序>5.order by 排序
<a class=header-anchor href=#5order-by-%e6%8e%92%e5%ba%8f></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>order</span> <span style=color:#66d9ef>by</span> salary <span style=color:#66d9ef>asc</span>; <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>默认升序排</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>order</span> <span style=color:#66d9ef>by</span> salary <span style=color:#66d9ef>desc</span>; <span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>降序排</span>

<span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>多条件排序</span>
<span style=color:#f92672>#</span><span style=color:#960050;background-color:#1e0010>先按照</span>age降序排<span style=color:#960050;background-color:#1e0010>，在年轻相同的情况下再按照薪资升序排</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>order</span> <span style=color:#66d9ef>by</span> age <span style=color:#66d9ef>desc</span>,salary <span style=color:#66d9ef>asc</span>;
</code></pre></div><h3 id=6limit-限制展示条数>6.limit 限制展示条数
<a class=header-anchor href=#6limit-%e9%99%90%e5%88%b6%e5%b1%95%e7%a4%ba%e6%9d%a1%e6%95%b0></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>限制展示条数</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>limit</span> <span style=color:#ae81ff>3</span>;
<span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>查询工资最高的人的详细信息</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>order</span> <span style=color:#66d9ef>by</span> salary <span style=color:#66d9ef>desc</span> <span style=color:#66d9ef>limit</span> <span style=color:#ae81ff>1</span>;

<span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>分页显示</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>limit</span> <span style=color:#ae81ff>0</span>,<span style=color:#ae81ff>5</span>;  <span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>limit</span> <span style=color:#ae81ff>5</span>,<span style=color:#ae81ff>5</span>;
</code></pre></div><p>###7.正则</p>
<div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>where</span> name regexp <span style=color:#e6db74>&#39;^j.*(n|y)$&#39;</span>;
</code></pre></div><h3 id=8replace-替换>8.replace 替换
<a class=header-anchor href=#8replace-%e6%9b%bf%e6%8d%a2></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#66d9ef>replace</span>(str1,<span style=color:#66d9ef>old</span>,<span style=color:#66d9ef>new</span>) <span style=color:#75715e>-- str1：需要替换的字段名
</span><span style=color:#75715e></span><span style=color:#66d9ef>update</span> gd_km <span style=color:#66d9ef>set</span> mc<span style=color:#f92672>=</span><span style=color:#66d9ef>replace</span><span style=color:#960050;background-color:#1e0010>（</span>mc,<span style=color:#e6db74>&#39;土地&#39;</span>,<span style=color:#e6db74>&#39;房子&#39;</span><span style=color:#960050;background-color:#1e0010>）</span>
<span style=color:#960050;background-color:#1e0010>说明：</span>new替换str1中出现的所有old<span style=color:#960050;background-color:#1e0010>，返回新的字符串，如果有某个参数为</span><span style=color:#66d9ef>NULL</span><span style=color:#960050;background-color:#1e0010>，此函数返回</span><span style=color:#66d9ef>NULL</span>
<span style=color:#960050;background-color:#1e0010>该函数可以多次替换，只要</span>str1中还有old存在<span style=color:#960050;background-color:#1e0010>，最后都被替换成</span><span style=color:#66d9ef>new</span>
<span style=color:#960050;background-color:#1e0010>若</span>new为空<span style=color:#960050;background-color:#1e0010>，则删除</span><span style=color:#66d9ef>old</span>
</code></pre></div><h2 id=多表查询>多表查询
<a class=header-anchor href=#%e5%a4%9a%e8%a1%a8%e6%9f%a5%e8%af%a2></a>
</h2><h3 id=1内连接左连接右连接全连接>1.内连接、左连接、右连接、全连接
<a class=header-anchor href=#1%e5%86%85%e8%bf%9e%e6%8e%a5%e5%b7%a6%e8%bf%9e%e6%8e%a5%e5%8f%b3%e8%bf%9e%e6%8e%a5%e5%85%a8%e8%bf%9e%e6%8e%a5></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#ae81ff>1</span><span style=color:#960050;background-color:#1e0010>、内连接：只取两张表有对应关系的记录（只拼两个表共有的）</span>
<span style=color:#960050;background-color:#1e0010>左表</span> <span style=color:#66d9ef>inner</span> <span style=color:#66d9ef>join</span> <span style=color:#960050;background-color:#1e0010>右表</span> <span style=color:#66d9ef>on</span> <span style=color:#960050;background-color:#1e0010>条件</span>
  <span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>inner</span> <span style=color:#66d9ef>join</span> dep <span style=color:#66d9ef>on</span> emp.dep_id <span style=color:#f92672>=</span> dep.id
       <span style=color:#66d9ef>where</span> dep.name <span style=color:#f92672>=</span> <span style=color:#e6db74>&#34;技术&#34;</span>;

<span style=color:#ae81ff>2</span><span style=color:#960050;background-color:#1e0010>、左连接：在内连接的基础上，保留左边的数据，右边没有就为空</span>
<span style=color:#960050;background-color:#1e0010>左表</span> <span style=color:#66d9ef>inner</span> <span style=color:#66d9ef>left</span> <span style=color:#960050;background-color:#1e0010>右表</span> <span style=color:#66d9ef>on</span> <span style=color:#960050;background-color:#1e0010>条件</span>
<span style=color:#ae81ff>3</span><span style=color:#960050;background-color:#1e0010>、右连接：在内连接的基础上，保留右边的数据，左边没有就为空</span>
<span style=color:#960050;background-color:#1e0010>左表</span> <span style=color:#66d9ef>inner</span> <span style=color:#66d9ef>right</span> <span style=color:#960050;background-color:#1e0010>右表</span> <span style=color:#66d9ef>on</span> <span style=color:#960050;background-color:#1e0010>条件</span>
<span style=color:#ae81ff>4</span><span style=color:#960050;background-color:#1e0010>、全连接：左右连接都有，用</span>union连接
<span style=color:#960050;background-color:#1e0010>左表</span> <span style=color:#66d9ef>inner</span> <span style=color:#66d9ef>left</span> <span style=color:#960050;background-color:#1e0010>右表</span> <span style=color:#66d9ef>on</span> <span style=color:#960050;background-color:#1e0010>条件</span> <span style=color:#66d9ef>union</span> <span style=color:#960050;background-color:#1e0010>左表</span> <span style=color:#66d9ef>inner</span> <span style=color:#66d9ef>right</span> <span style=color:#960050;background-color:#1e0010>右表</span> <span style=color:#66d9ef>on</span> <span style=color:#960050;background-color:#1e0010>条件</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>left</span> <span style=color:#66d9ef>join</span> dep <span style=color:#66d9ef>on</span> emp.dep_id <span style=color:#f92672>=</span> dep.id
<span style=color:#66d9ef>union</span>
<span style=color:#66d9ef>select</span> <span style=color:#f92672>*</span> <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>right</span> <span style=color:#66d9ef>join</span> dep <span style=color:#66d9ef>on</span> emp.dep_id <span style=color:#f92672>=</span> dep.id;
</code></pre></div><h3 id=2子查询>2.子查询
<a class=header-anchor href=#2%e5%ad%90%e6%9f%a5%e8%af%a2></a>
</h3><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:2;-o-tab-size:2;tab-size:2><code class=language-sql data-lang=sql><span style=color:#f92672>#</span> <span style=color:#960050;background-color:#1e0010>就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用</span>
<span style=color:#66d9ef>select</span> name <span style=color:#66d9ef>from</span> <span style=color:#66d9ef>where</span> id <span style=color:#66d9ef>in</span>(<span style=color:#66d9ef>select</span> dep_id <span style=color:#66d9ef>from</span> emp <span style=color:#66d9ef>where</span> age<span style=color:#f92672>&gt;</span><span style=color:#ae81ff>25</span>);
</code></pre></div>
</div>
<footer class=post-footer>
<div class=post-tags>
<a href=/tags/mysql>
mysql
</a>
<a href=/tags/php>
php
</a>
</div>
<div class=addthis_inline_share_toolbox style=text-align:center></div>
<hr>
<div class=post-nav>
<div class="post-nav-next post-nav-item">
<a href=/post/2022-12-21-mysqlyuju.html rel=next title=js和php做身份证正误判断>
<i class="fa fa-chevron-left"></i> js和php做身份证正误判断
</a>
</div>
<div class="post-nav-prev post-nav-item">
<a href=/post/custom-files.html rel=prev title=支持用户自定义设计>
支持用户自定义设计
<i class="fa fa-chevron-right"></i>
</a>
</div>
</div>
</footer>
</article>
</div>
</div>
</main>
<footer class=footer>
<div class=footer-inner>
<div class=copyright>
&copy;
<span itemprop=copyrightYear>
2010 - 2023
</span>
<span class=with-love>
<i class="fa fa-heart"></i>
</span>
<span class=author itemprop=copyrightHolder>NexT 主题</span>
</div>
<div class=powered-by>
由 <a href=https://gohugo.io title=0.92.2 target=_blank>Hugo</a> & <a href=https://github.com/hugo-next/hugo-theme-next title=4.5.3 target=_blank>Hugo NexT.Gemini</a> 强力驱动
</div>
</div>
</footer>
<script type=text/javascript src=https://cdn.staticfile.org/animejs/3.2.1/anime.min.js defer></script>
<script type=text/javascript src=https://cdn.staticfile.org/viewerjs/1.11.0/viewer.min.js defer></script>
<script class=next-config data-name=main type=application/json>{"bookmark":{"color":"#222","enable":true,"save":"manual"},"copybtn":true,"darkmode":true,"giscus":{"cfg":{"category":"Comments","categoryid":null,"emit":false,"inputposition":"top","mapping":"title","reactions":false,"repo":"username/repo-name","repoid":null,"theme":"preferred_color_scheme"},"js":"https://giscus.app/client.js"},"hostname":"https://dzmm.gitee.io","i18n":{"ds_day":" 天前","ds_days":" 天 ","ds_hour":" 小时前","ds_hours":" 小时 ","ds_just":"刚刚","ds_min":" 分钟前","ds_mins":" 分钟","ds_month":" 个月前","ds_years":" 年 ","empty":"没有找到任何搜索结果：${query}","hits":"找到 ${hits} 个搜索结果","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","placeholder":"搜索..."},"lang":"zh-CN","lazyload":false,"localSearch":{"enable":true,"limit":1e3,"path":"/searchindexes.xml","preload":false,"topnperarticle":-1,"trigger":"auto","unescape":false},"motion":{"async":true,"enable":true,"transition":{"collheader":"fadeInLeft","postblock":"fadeIn","postbody":"fadeInDown","postheader":"fadeInDown","sidebar":"fadeInUp"}},"postmeta":{"comments":{"enable":false,"plugin":"waline"},"views":{"enable":true,"plugin":"busuanzi"}},"root":"/","scheme":"Gemini","sidebar":{"display":"post","offset":12,"padding":18,"position":"left","width":256},"vendor":{"plugins":"qiniu","router":"https://cdn.staticfile.org"},"version":"4.5.3","waline":{"cfg":{"emoji":false,"imguploader":false,"placeholder":"请文明发言哟 ヾ(≧▽≦*)o","reaction":true,"reactiontext":["点赞","踩一下","得意","不屑","尴尬","睡觉"],"reactiontitle":"你认为这篇文章怎么样？","requiredmeta":["nick","mail"],"serverurl":null,"sofa":"快来发表你的意见吧 (≧∀≦)ゞ","wordlimit":200},"css":{"alias":"waline","file":"dist/waline.css","name":"@waline/client","version":"2.13.0"},"js":{"alias":"waline","file":"dist/waline.js","name":"@waline/client","version":"2.13.0"}}}</script>
<script type=text/javascript src=/js/main.min.da1a84618d05ef38d3aaca7a8e93f6433f9bb147d8ffcc3a840de8a03dc5ff9f.js defer></script>
</body>
</html>